/*
 * Created on 6 �.�. 2548
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package com.softplus.carrefour.hd.bltier.report;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.text.DecimalFormat;
import java.text.SimpleDateFormat;

import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import java.util.TreeSet;
import java.util.Vector;

import net.sf.hibernate.HibernateException;
import net.sf.hibernate.Query;
import net.sf.hibernate.Session;

import com.softplus.carrefour.CFException;
import com.softplus.carrefour.InfrastructureException;
import com.softplus.carrefour.hd.bltier.db.HibernateUtil;
import com.softplus.carrefour.hd.bltier.entity.CfhdCaseHead;
import com.softplus.carrefour.hd.bltier.entity.UserLevel;

/**
 * @author Kowit
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class CCaseReportManage implements ICaseReportViewer {
    public Vector listCaseByTypeAndStatus(String countryID, String caseType, 
                                          String caseStatus, Date from, 
                                          Date to) throws CFException {

        if (caseType == null || caseStatus == null || from == null || 
            to == null)
            return new Vector();

        List ls = null;
        Session session = HibernateUtil.getSession();

        String orderBy = 
            " order by c.country ,c.caseType, trunc(c.openCaseDate), c.caseStatus ,c.severity ";
        String countryCond = "";
        String caseCond = "";
        String statusCond = "";
        String dateCond = "";

        if (countryID != null && !countryID.equals("") && 
            !countryID.equals("ALL"))
            countryCond = " and c.countryId = '" + countryID + "' ";

        if (caseType != null && !caseType.equals("") && 
            !caseType.equals("ALL"))
            caseCond = " and c.caseType = '" + caseType + "' ";

        if (caseStatus != null && !caseStatus.equals("") && 
            !caseStatus.equals("ALL"))
            statusCond = " and c.caseStatus in (" + caseStatus + ") ";

        boolean isFrom = false;
        boolean isTo = false;

        if (from != null || to != null) {
            if (from != null && to != null) {
                dateCond = 
                        " and trunc(c.openCaseDate) >= trunc(:fromDate) and trunc(c.openCaseDate)  <= trunc(:toDate) ";
                isFrom = true;
                isTo = true;
            } else if (from != null) {
                dateCond = " and c.openCaseDate >= :fromDate ";
                isFrom = true;
            } else {
                dateCond = " and c.openCaseDate <= :toDate ";
                isTo = true;
            }
        }

        String hql = 
            "from CfhdCaseHead c where (1=1) " + countryCond + caseCond + 
            statusCond + dateCond + orderBy;

        try {
            Query query = session.createQuery(hql);

            if (isFrom)
                query.setDate("fromDate", from);
            if (isTo)
                query.setDate("toDate", to);

            ls = query.list();

            Collections.sort(ls, new CaseComparator());

        } catch (HibernateException e) {
            e.printStackTrace();
        } finally {
            HibernateUtil.closeSession();
        }
        return new Vector(ls);
    }

    public Vector listCaseBySystemAndModule(String countryID, String caseType, 
                                            String system, String module, 
                                            Date from, 
                                            Date to) throws CFException {

        if (caseType == null || from == null || to == null)
            return new Vector();

        List ls = null;
        Session session = HibernateUtil.getSession();

        String orderBy = 
            " order by c.cfhdSystem, c.country ,c.caseType, trunc(c.openCaseDate), c.caseStatus ,c.severity ";
        String countryCond = "";
        String caseCond = "";
        String systemCond = "";
        String moduleCond = "";
        String dateCond = "";

        if (countryID != null && !countryID.equals("") && 
            !countryID.equals("ALL"))
            countryCond = " and c.countryId = '" + countryID + "' ";

        if (caseType != null && !caseType.equals("") && 
            !caseType.equals("ALL"))
            caseCond = " and c.caseType = '" + caseType + "' ";

        if (system == null) {
            systemCond = " and c.cfhdSystem is null";
        } else if (system != null && !system.equals("") && 
                   !system.equals("ALL"))
            systemCond = " and c.cfhdSystem = '" + system + "'";


        if (module != null && !module.equals("") && !module.equals("ALL"))
            moduleCond = " and c.cfhdModule = '" + module + "'";


        boolean isFrom = false;
        boolean isTo = false;

        if (from != null || to != null) {
            if (from != null && to != null) {
                dateCond = 
                        " and trunc(c.openCaseDate) >= trunc(:fromDate) and trunc(c.openCaseDate)  <= trunc(:toDate) ";
                isFrom = true;
                isTo = true;
            } else if (from != null) {
                dateCond = " and c.openCaseDate >= :fromDate ";
                isFrom = true;
            } else {
                dateCond = " and c.openCaseDate <= :toDate ";
                isTo = true;
            }
        }

        String hql = 
            "from CfhdCaseHead c where (1=1) " + countryCond + caseCond + 
            systemCond + moduleCond + dateCond + orderBy;

        try {
            Query query = session.createQuery(hql);

            if (isFrom)
                query.setDate("fromDate", from);
            if (isTo)
                query.setDate("toDate", to);

            ls = query.list();

            Collections.sort(ls, new SystemCaseComparator());

        } catch (HibernateException e) {
            e.printStackTrace();
        } finally {
            HibernateUtil.closeSession();
        }
        return new Vector(ls);
    }
    //    public Vector listAutoEscalateCase(String countryID,String caseType,String fromLevel,String toLevel,Date from,Date to) throws CFException {
    //        
    //        if(caseType == null || from == null || to == null)
    //            return new Vector();
    //        
    //        List ls = null;
    //        Session session = HibernateUtil.getSession();
    //        
    //        String orderBy = " order by c.country ,c.caseType, c.openCaseDate, c.caseStatus ,c.severity ";
    //        String countryCond = "";
    //        String caseCond = "";
    //        String fromLevelCond = "";
    //        String toLevelCond = "";
    //        String dateCond = "";
    //        boolean joinDetail = false;
    //        boolean joinUserLevel = false;
    //        if(countryID != null && !countryID.equals("") && !countryID.equals("ALL"))
    //            countryCond = " and c.countryId = '"+countryID+"' ";
    //        
    //        if(caseType != null && !caseType.equals("") && !caseType.equals("ALL"))
    //            caseCond = " and c.caseType in ("+caseType+") ";
    //        
    //        if(fromLevel != null && !fromLevel.equals("") && !fromLevel.equals("ALL")){
    //            fromLevelCond = " and ul.ulevel = '"+fromLevel+"'";
    //            joinDetail = true;
    //            joinUserLevel = true;
    //        }
    //        if(toLevel != null && !toLevel.equals("") && !toLevel.equals("ALL")){
    //            toLevelCond = " and d.transferToLv = '"+toLevel+"'";
    //            joinDetail = true;
    //        }
    //        
    //        boolean isFrom = false;
    //        boolean isTo = false;
    //        
    //        if(from != null || to != null){
    //            if(from != null && to != null){
    //                dateCond = " and trunc(c.openCaseDate) >= trunc(:fromDate) and trunc(c.openCaseDate)  <= trunc(:toDate) ";
    //                isFrom = true;
    //                isTo = true;
    //            }else if(from != null){
    //                dateCond = " and c.openCaseDate >= :fromDate ";
    //                isFrom = true;
    //            }else {
    //                dateCond = " and c.openCaseDate <= :toDate ";
    //                isTo = true;
    //            }
    //        }
    //        
    //        String hql = "";
    //        String whereConds = countryCond+caseCond+fromLevelCond+toLevelCond+dateCond+orderBy;
    //        if(joinDetail && joinUserLevel)
    //            hql = "select c from CfhdCaseHead c,CfhdCaseDetail d,UserLevel ul where (c.caseId = d.comp_id.caseId) and (c.userId = ul.userId)  and  (d.comp_id.caseDetSeq = (select max(dd.comp_id.caseDetSeq) from CfhdCaseDetail dd where dd.comp_id.caseId = c.caseId )) and c.autoEscalateStatus = 'Y' "+whereConds;
    //        else if(joinDetail && !joinUserLevel)
    //            hql = "select c from CfhdCaseHead c,CfhdCaseDetail d where (c.caseId = d.comp_id.caseId)  and  (d.comp_id.caseDetSeq = (select max(dd.comp_id.caseDetSeq) from CfhdCaseDetail dd where dd.comp_id.caseId = c.caseId )) and c.autoEscalateStatus = 'Y' "+whereConds;
    //        else
    //            hql = "select c from CfhdCaseHead c  where   c.autoEscalateStatus = 'Y' "+whereConds;
    //        try {
    //            Query query = session.createQuery(hql);
    //            
    //            if(isFrom)
    //                query.setDate("fromDate",from);
    //            if(isTo)
    //                query.setDate("toDate",to);
    //            
    //            ls = query.list();
    //            
    //        } catch (HibernateException e) {
    //            e.printStackTrace();
    //        }finally{            
    //            HibernateUtil.closeSession();
    //        }
    //        return new Vector(ls);
    //    }

    public Vector listAutoEscalateCase(String countryID, String caseType, 
                                       String fromLevel, String toLevel, 
                                       Date from, Date to) throws CFException {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        if (caseType == null || from == null || to == null)
            return new Vector();

        SimpleDateFormat ft = new SimpleDateFormat("dd/MM/yyyy");

        String fromDateStr = ft.format(from);
        String toDateStr = ft.format(to);
        String orderBy = 
            " order by ch.country_id ,ch.case_type, trunc(ch.open_case_date), ch.case_status ,ch.severity ";
        Vector result = new Vector();
        try {
            String sql = 
                "SELECT ch.case_id case_id, ch.subject SUBJECTH, " + "       ch.open_case_date open_case_date, ch.case_type case_type," + 
                "       (SELECT cdl.cred_server_time" + 
                "          FROM cfhd_case_detail_log cdl" + 
                "         WHERE cdl.case_id = cd.case_id" + 
                "           AND cdl.case_det_seq = cd.case_det_seq" + 
                "           AND cdl.seq_log =" + 
                "                  (SELECT MAX (seq_log)" + 
                "                     FROM cfhd_case_detail_log" + 
                "                    WHERE case_id = cdl.case_id" + 
                "                      AND case_det_seq = cdl.case_det_seq)) AS server_time," + 
                "       (SELECT cdl.cred_local_time" + 
                "          FROM cfhd_case_detail_log cdl" + 
                "         WHERE cdl.case_id = cd.case_id" + 
                "           AND cdl.case_det_seq = cd.case_det_seq" + 
                "           AND cdl.seq_log =" + 
                "                  (SELECT MAX (seq_log)" + 
                "                     FROM cfhd_case_detail_log" + 
                "                    WHERE case_id = cdl.case_id" + 
                "                      AND case_det_seq = cdl.case_det_seq)) AS local_time" + 
                "  FROM cfhd_case_detail cd, cfhd_case_head ch" + 
                "  WHERE (1 = 1)" + "   AND ch.case_id = cd.case_id" + 
                "   AND ch.case_type in  (" + caseType + ")" + 
                "   AND ch.country_id = '" + countryID + "'" + 
                "   AND cd.transfer_to_lv = '" + toLevel + "'" + 
                "   AND cd.case_action = 'ATE'" + 
                "   AND trunc(ch.open_case_date) >= to_date('" + fromDateStr + 
                "','DD/MM/YYYY') AND trunc(ch.open_case_date) <= to_date('" + 
                toDateStr + "','DD/MM/YYYY')" + "   AND (EXISTS (" + 
                "           SELECT 1" + 
                "             FROM cfhd_case_detail ccd" + 
                "            WHERE ccd.case_id = cd.case_id" + 
                "              AND ccd.case_det_seq = (cd.case_det_seq - 1)" + 
                "              AND ccd.transfer_to_lv = '" + fromLevel + "')" + 
                "       ) ";
            conn = HibernateUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql + orderBy);
            CaseReportVO vo = null;
            while (rs.next()) {
                vo = new CaseReportVO();
                vo.setCaseId(rs.getString("CASE_ID"));
                vo.setSubject(rs.getString("SUBJECTH"));
                vo.setCaseType(rs.getString("CASE_TYPE"));
                vo.setOpenDate(new Date(rs.getTimestamp("OPEN_CASE_DATE").getTime()));
                vo.setLocalSubmitTime(new Date(rs.getTimestamp("LOCAL_TIME").getTime()));
                vo.setServerSubmitTime(new Date(rs.getTimestamp("SERVER_TIME").getTime()));
                result.add(vo);
            }

            stmt.close();
        } catch (InfrastructureException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            HibernateUtil.closeSession();
        }
        return result;

    }
    /*
     * TODO change fix date to sysdate
     */

    public Vector listCaseStillOpen(String countryID, String caseType, 
                                    String openLen, boolean isClose, Date from, 
                                    Date to) throws CFException {

        if (caseType == null || from == null || to == null)
            return new Vector();

        List ls = null;
        Session session = HibernateUtil.getSession();

        String orderBy = 
            " order by c.country_id ,c.case_type, trunc(c.open_case_date), c.case_status ,c.severity ";
        String countryCond = "";
        String caseCond = "";
        String openMoreThanCond = "";
        String closeCond = "";
        String dateCond = "";

        if (isClose) {
            closeCond = 
                    " and c.case_status = '" + CfhdCaseHead.STATUS_CLOSE + "' ";
        } else {
            closeCond = 
                    " and c.case_status != '" + CfhdCaseHead.STATUS_CLOSE + "' ";
        }

        if (countryID != null && !countryID.equals("") && 
            !countryID.equals("ALL"))
            countryCond = " and c.country_id  = '" + countryID + "'";

        if (caseType != null && !caseType.equals("") && 
            !caseType.equals("ALL"))
            caseCond = " and c.case_type in (" + caseType + ") ";

        String compareEx = "<";
        if (openLen.equals("0"))
            compareEx = "<=";

        if (openLen != null && !openLen.equals(""))
            openMoreThanCond = 
                    " and trunc(c.open_case_date) " + compareEx + " trunc(((sysdate + (select time_shift from timezone where country_id = c.country_id)/24) - " + 
                    openLen + ")) "; // --> change to sysdate

        boolean isFrom = false;
        boolean isTo = false;


        if (from != null || to != null) {
            if (from != null && to != null) {
                dateCond = 
                        " and trunc(c.open_case_date) >= trunc(:fromDate) and trunc(c.open_case_date)  <= trunc(:toDate) ";
                isFrom = true;
                isTo = true;
            } else if (from != null) {
                dateCond = " and c.open_case_date >= :fromDate ";
                isFrom = true;
            } else {
                dateCond = " and c.open_case_date <= :toDate ";
                isTo = true;
            }
        }

        String sql = 
            "select {c.*}  from CFHD_CASE_HEAD  c where (1=1)  " + countryCond + 
            caseCond + openMoreThanCond + closeCond + dateCond + orderBy;

        try {
            Query query = session.createSQLQuery(sql, "c", CfhdCaseHead.class);

            if (isFrom)
                query.setDate("fromDate", from);
            if (isTo)
                query.setDate("toDate", to);


            ls = query.list();

            Collections.sort(ls, new CaseComparator());

            HashMap localTimes = new HashMap();
            long diff = 0;
            Date d1 = null;
            Date d2 = null;
            for (Iterator iter = ls.iterator(); iter.hasNext(); ) {
                CfhdCaseHead ch = (CfhdCaseHead)iter.next();
                d1 = ch.getOpenCaseDate();
                d2 = (Date)localTimes.get(ch.getCountryId());
                if (d2 == null) {
                    d2 = this.getLocaltimeByCountry(ch.getCountryId());
                    localTimes.put(ch.getCountryId(), d2);
                }
                if (d1.after(d2)) {
                    //throw new CFException(new Exception("Close case date must after open"));     
                    System.out.println("open date : " + d1);
                    System.out.println("local date : " + d2);
                    ch.setDuration("error");
                } else {
                    diff = d2.getTime() - d1.getTime();
                    long day = diff / (1000 * 60 * 60 * 24);
                    diff = diff % (1000 * 60 * 60 * 24);
                    long hour = diff / (1000 * 60 * 60);
                    diff = diff % (1000 * 60 * 60);
                    long mint = diff / (1000 * 60);
                    DecimalFormat dc = new DecimalFormat("#");

                    ch.setDuration("" + dc.format(day) + "D. " + 
                                   dc.format(hour) + ":" + dc.format(mint) + 
                                   "hrs");
                }

            }

        } catch (HibernateException e) {
            e.printStackTrace();
        } finally {
            HibernateUtil.closeSession();
        }
        return new Vector(ls);
    }

    public Vector listTotalTimeConsume(String countryID, String caseType, 
                                       Date from, Date to) throws CFException {

        if (caseType == null || from == null || to == null)
            return new Vector();

        List ls = null;
        Session session = HibernateUtil.getSession();

        String orderBy = 
            " order by c.country ,c.caseType, trunc(c.openCaseDate), c.caseStatus ,c.severity ";
        String countryCond = "";
        String caseCond = "";
        String dateCond = "";

        if (countryID != null && !countryID.equals("") && 
            !countryID.equals("ALL"))
            countryCond = " and c.countryId = '" + countryID + "' ";

        if (caseType != null && !caseType.equals("") && 
            !caseType.equals("ALL"))
            caseCond = " and c.caseType = '" + caseType + "' ";


        boolean isFrom = false;
        boolean isTo = false;

        if (from != null || to != null) {
            if (from != null && to != null) {
                dateCond = 
                        " and trunc(c.openCaseDate) >= trunc(:fromDate) and trunc(c.openCaseDate)  <= trunc(:toDate) ";
                isFrom = true;
                isTo = true;
            } else if (from != null) {
                dateCond = " and c.openCaseDate >= :fromDate ";
                isFrom = true;
            } else {
                dateCond = " and c.openCaseDate <= :toDate ";
                isTo = true;
            }
        }

        String hql = 
            "from CfhdCaseHead c where (c.caseStatus = '" + CfhdCaseHead.STATUS_CLOSE + 
            "') " + countryCond + caseCond + dateCond + orderBy;

        try {
            Query query = session.createQuery(hql);

            if (isFrom)
                query.setDate("fromDate", from);
            if (isTo)
                query.setDate("toDate", to);

            ls = query.list();

            Collections.sort(ls, new CaseComparator());

            long diff = 0;
            Date d1 = null;
            Date d2 = null;
            for (Iterator iter = ls.iterator(); iter.hasNext(); ) {
                CfhdCaseHead ch = (CfhdCaseHead)iter.next();
                d1 = ch.getOpenCaseDate();
                d2 = ch.getCloseCaseDate();
                if (d1 == null || d2 == null || d1.after(d2)) {
                    //throw new CFException(new Exception("Close case date must after open"));     
                    // ch.setDuration("error");
                } else {
                    diff = d2.getTime() - d1.getTime();
                    long day = diff / (1000 * 60 * 60 * 24);
                    diff = diff % (1000 * 60 * 60 * 24);
                    long hour = diff / (1000 * 60 * 60);
                    diff = diff % (1000 * 60 * 60);
                    long mint = diff / (1000 * 60);
                    DecimalFormat dc = new DecimalFormat("#");
                    ch.setDuration("" + dc.format(day) + "D. " + 
                                   dc.format(hour) + ":" + dc.format(mint) + 
                                   "hrs");
                }
            }

        } catch (HibernateException e) {
            e.printStackTrace();
        } finally {
            HibernateUtil.closeSession();
        }
        return new Vector(ls);
    }

    public Date getLocaltimeByCountry(String countryID) throws CFException {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Date ltime = null;
        try {
            String sql = 
                "select (sysdate + (time_shift/24)) as LTIME from timezone where country_id =  '" + 
                countryID + "'";
            conn = HibernateUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next())
                ltime = new Date(rs.getTimestamp("LTIME").getTime());
            stmt.close();
        } catch (InfrastructureException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            HibernateUtil.closeSession();
        }
        return ltime;
    }

    public Date getServerTimeAndLocalTime(String caseID) throws CFException {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Date ltime = null;
        try {
            String sql = "";
            conn = HibernateUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next())
                ltime = rs.getTimestamp("");
            stmt.close();
        } catch (InfrastructureException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            HibernateUtil.closeSession();
        }
        return ltime;
    }

    public boolean isHasAutoEscalateCase(String caseType, String fromLevel, 
                                         String toLevel, Date from, 
                                         Date to) throws CFException {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        boolean result = false;
        if (caseType == null || from == null || to == null)
            return result;

        SimpleDateFormat ft = new SimpleDateFormat("dd/MM/yyyy");

        String fromDateStr = ft.format(from);
        String toDateStr = ft.format(to);

        try {
            String sql = 
                "SELECT ch.case_id " + "  FROM cfhd_case_detail cd, cfhd_case_head ch" + 
                "  WHERE (1 = 1)" + "   AND ch.case_id = cd.case_id" + 
                "   AND ch.case_type in  (" + caseType + ")" + 
                "   AND cd.transfer_to_lv = '" + toLevel + "'" + 
                "   AND cd.case_action = 'ATE'" + 
                "   AND trunc(ch.open_case_date) >= to_date('" + fromDateStr + 
                "','DD/MM/YYYY') AND trunc(ch.open_case_date) <= to_date('" + 
                toDateStr + "','DD/MM/YYYY')" + "   AND (EXISTS (" + 
                "           SELECT 1" + 
                "             FROM cfhd_case_detail ccd" + 
                "            WHERE ccd.case_id = cd.case_id" + 
                "              AND ccd.case_det_seq = (cd.case_det_seq - 1)" + 
                "              AND ccd.transfer_to_lv = '" + fromLevel + "')" + 
                "       ) ";
            conn = HibernateUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next())
                result = true;
            stmt.close();
        } catch (InfrastructureException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            HibernateUtil.closeSession();
        }
        return result;
    }
}
